package com.arethusa;

import javax.swing.JPanel;
import java.awt.BorderLayout;
import java.awt.GridBagLayout;
import javax.swing.JButton;
import java.awt.GridBagConstraints;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import javax.swing.JCheckBox;
import javax.swing.JLabel;
import javax.swing.JComboBox;
import javax.swing.JOptionPane;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.view.JRViewer;

import simpleorm.core.SConnection;
import simpleorm.core.SPreparedStatement;
import simpleorm.core.SResultSet;

import com.arethusa.langebaandb.*;

import java.awt.Insets;

public class Lijsten extends JPanel {

	private static final long serialVersionUID = 1L;
	private JPanel selectiePanel = null;
	private JButton startLijstenButton = null;
	private JCheckBox perProgrammaCheckBox = null;
	private JLabel programmaLabel = null;
	private JComboBox programmaComboBox = null;
	private int programmaNr = 0;
	private int depotNr = 0;
	private JPanel previewPanel = null;
	private JCheckBox perVerenigingCheckBox = null;
	private JLabel verenigingLabel = null;
	private JComboBox verenigingComboBox = null;
	private JButton startsPerVerenigingButton = null;
	private JButton aantalDeelnemersPerVerenigingButton = null;
	private JButton statistiekenButton = null;
	private JButton factuurButton = null;
	private JButton uitslagButton = null;
	private JButton jButtonBoekje = null;
	private class ProgrammaComboActionListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
	        try {
				JComboBox cb = (JComboBox)arg0.getSource();
				String pnr = ((String)cb.getSelectedItem()).substring(0,2);
				programmaNr = (pnr.equalsIgnoreCase("<K")?0:new Integer(pnr).intValue());
			} catch (NumberFormatException e) {
				e.printStackTrace();
			}
		}
	}
	
	private class VerenigingComboActionListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
	        try {
				JComboBox cb = (JComboBox)arg0.getSource();
				String dnr = ((String)cb.getSelectedItem()).substring(0,5);
				depotNr = (dnr.equalsIgnoreCase("<Kies")?0:new Integer(dnr).intValue());
			} catch (NumberFormatException e) {
				e.printStackTrace();
			}
		}
	}
	
	private class PerProgrammaCheckBoxActionListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			JCheckBox cb = (JCheckBox)arg0.getSource();
			if( cb.isSelected() ) {
				programmaLabel.setEnabled(true);
				programmaComboBox.setEnabled(true);
				programmaComboBox.setSelectedIndex(0);
			} else {
				programmaLabel.setEnabled(false);
				programmaComboBox.setEnabled(false);
				programmaComboBox.setSelectedIndex(0);
			}
		}
		
	}
	
	private class PerVerenigingCheckBoxActionListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			JCheckBox cb = (JCheckBox)arg0.getSource();
			if( cb.isSelected() ) {
				verenigingLabel.setEnabled(true);
				verenigingComboBox.setEnabled(true);
				verenigingComboBox.setSelectedIndex(0);
			} else {
				verenigingLabel.setEnabled(false);
				verenigingComboBox.setEnabled(false);
				verenigingComboBox.setSelectedIndex(0);
			}
		}
		
	}
	
	private class BoekjeButtonActionListener implements ActionListener {

		public void actionPerformed(ActionEvent e) {
			ExportBoekje boekje = new ExportBoekje();
			boekje.exportDeelnemendeVerenigingen();
		}
		
	}
	
	private class StartLijstenButtonActionListener implements ActionListener {

		public void actionPerformed(ActionEvent arg0) {
			PrintPreviewDialog prvw = new PrintPreviewDialog();
			Map<String, Integer> parms = null;
			String lijst = null;
			if( perProgrammaCheckBox.isSelected() ) {
				parms = new HashMap<String, Integer>();
				parms.put("ProgrammaNummer", new Integer(programmaNr));
				lijst = "reports\\StartLijst1Prog.jasper";
			} else {
				parms = null;
				lijst = "reports\\StartLijst.jasper";
			}
			
			JasperPrint prt = new JasperPrint();
			try {
				SConnection.begin();
				prt = JasperFillManager.fillReport(lijst, parms, SConnection.getBegunDBConnection());
				SConnection.commit();
			} catch (JRException re) {
				JOptionPane.showMessageDialog(null,
						re.getMessage()+re.getCause(),
						"Report foutmelding", JOptionPane.ERROR_MESSAGE);
				SConnection.commit();
			}
			if( previewPanel != null) prvw.remove(previewPanel);
			previewPanel = new JRViewer(prt);
			prvw.getContentPane().add(previewPanel, java.awt.BorderLayout.CENTER);
			prvw.setSize(800,950);
			prvw.setVisible(true);
		}
	}

	private class StartsPerVerenigingButtonActionListener implements ActionListener {

		@SuppressWarnings("unchecked")
		public void actionPerformed(ActionEvent arg0) {
			BerekenKostenPerVereniging();
			PrintPreviewDialog prvw = new PrintPreviewDialog();
			Map parms = null;
			parms = new HashMap();
			parms.put("prijsPerStart", 0.0);
			String lijst = null;
			if( perVerenigingCheckBox.isSelected() ) {
				parms.put("DepotNummer", new Integer(depotNr));
				lijst = "reports\\StartsVoor1Vereniging.jasper";
			} else {
				lijst = "reports\\StartsPerVereniging.jasper";
			}
			
			JasperPrint prt = new JasperPrint();
			try {
				SConnection.begin();
				prt = JasperFillManager.fillReport(lijst, parms, SConnection.getBegunDBConnection());
				SConnection.commit();
			} catch (JRException re) {
				JOptionPane.showMessageDialog(null,
						re.getMessage()+re.getCause(),
						"Report foutmelding", JOptionPane.ERROR_MESSAGE);
				SConnection.commit();
			}
			if( previewPanel != null) prvw.remove(previewPanel);
			previewPanel = new JRViewer(prt);
			prvw.getContentPane().add(previewPanel, java.awt.BorderLayout.CENTER);
			prvw.setSize(800,950);
			prvw.setVisible(true);
		}
	}
	
	private class AantalDeelnemersPerVerenigingButtonActionListener implements ActionListener {

		@SuppressWarnings("unchecked")
		public void actionPerformed(ActionEvent arg0) {
			PrintPreviewDialog prvw = new PrintPreviewDialog();
			Map parms = null;
			String lijst = null;
			lijst = "reports\\AantalDeelnemersPerVereniging.jasper";
			
			JasperPrint prt = new JasperPrint();
			try {
				SConnection.begin();
				prt = JasperFillManager.fillReport(lijst, parms, SConnection.getBegunDBConnection());
				SConnection.commit();
			} catch (JRException re) {
				JOptionPane.showMessageDialog(null,
						re.getMessage()+re.getCause(),
						"Report foutmelding", JOptionPane.ERROR_MESSAGE);
				SConnection.commit();
			}
			if( previewPanel != null) prvw.remove(previewPanel);
			previewPanel = new JRViewer(prt);
			prvw.getContentPane().add(previewPanel, java.awt.BorderLayout.CENTER);
			prvw.setSize(800,950);
			prvw.setVisible(true);
		}
	}
	
	private class StatistiekenButtonActionListener implements ActionListener {

		@SuppressWarnings("unchecked")
		public void actionPerformed(ActionEvent arg0) {
			Helper.BepaalStatistieken();
			PrintPreviewDialog prvw = new PrintPreviewDialog();
			Map parms = null;
			String lijst = null;
			lijst = "reports\\Statistieken.jasper";
			
			JasperPrint prt = new JasperPrint();
			try {
				SConnection.begin();
				prt = JasperFillManager.fillReport(lijst, parms, SConnection.getBegunDBConnection());
				SConnection.commit();
			} catch (JRException re) {
				JOptionPane.showMessageDialog(null,
						re.getMessage()+re.getCause(),
						"Report foutmelding", JOptionPane.ERROR_MESSAGE);
				SConnection.commit();
			}
			if( previewPanel != null) prvw.remove(previewPanel);
			previewPanel = new JRViewer(prt);
			prvw.getContentPane().add(previewPanel, java.awt.BorderLayout.CENTER);
			prvw.setSize(800,950);
			prvw.setVisible(true);
		}
	}
	
	private class FactuurButtonActionListener implements ActionListener {

		@SuppressWarnings("unchecked")
		public void actionPerformed(ActionEvent arg0) {
			boolean hasBegun = SConnection.getConnection().hasBegun();
			BerekenKostenPerVereniging();
			PrintPreviewDialog prvw = new PrintPreviewDialog();
			Map parms = null;
			parms = new HashMap();
			String lijst = null;
			lijst = "reports\\Factuur.jasper";
			
			JasperPrint prt = new JasperPrint();
			try {
				
				if( ! hasBegun ) { SConnection.begin(); }
				Connection JDBCcon = SConnection.getBegunDBConnection();
				Statement stmt = JDBCcon.createStatement();
				// Nr of Leencaps
				ResultSet res = stmt.executeQuery("SELECT SUM(Kosten1+Kosten2+Kosten3) AS TotaalHSZ, SUM(Kosten4) AS TotaalONK FROM kostenperver;");
				if( res.next() ) {
					parms.put("totaalHSZ", res.getDouble("TotaalHSZ"));
					parms.put("totaalONK", res.getDouble("TotaalONK"));
				}
				prt = JasperFillManager.fillReport(lijst, parms, SConnection.getBegunDBConnection());
				if( ! hasBegun ) { SConnection.commit(); }
			} catch (JRException re) {
				JOptionPane.showMessageDialog(null,
						re.getMessage()+re.getCause(),
						"Report foutmelding", JOptionPane.ERROR_MESSAGE);
				SConnection.commit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			if( previewPanel != null) prvw.remove(previewPanel);
			previewPanel = new JRViewer(prt);
			prvw.getContentPane().add(previewPanel, java.awt.BorderLayout.CENTER);
			prvw.setSize(800,950);
			prvw.setVisible(true);
		}
	}
	
	private class UitslagButtonActionListener implements ActionListener {

		public void actionPerformed(ActionEvent ae) {
			boolean hasBegun = SConnection.getConnection().hasBegun();
			// Bepaal eerst de statistieken : #starts, #na, #ng, #ziek
			Helper.BepaalNrStarts();
			Helper.BepaalNrNa();
			Helper.BepaalNrNg();
			Helper.BepaalNrZiek();
			// en print vervolgens
			PrintPreviewDialog prvw = new PrintPreviewDialog();
			
			JasperPrint prt = new JasperPrint();
			try {
				if( ! hasBegun ) { SConnection.begin(); }
				prt = JasperFillManager.fillReport("reports\\UitslagTotaal.jasper", null, SConnection.getBegunDBConnection());
				if( ! hasBegun ) { SConnection.commit(); }
			} catch (JRException re) {
				JOptionPane.showMessageDialog(null,
						re.getMessage()+re.getCause(),
						"Report foutmelding", JOptionPane.ERROR_MESSAGE);
			}
			if( previewPanel != null) prvw.getContentPane().remove(previewPanel);
			previewPanel = new JRViewer(prt);
			prvw.getContentPane().add(previewPanel, java.awt.BorderLayout.CENTER);
			prvw.setSize(800,950);
			prvw.setVisible(true);
		}
		
	}
	
	/**
	 * This is the default constructor
	 */
	public Lijsten() {
		super();
		initialize();
	}

	/**
	 * This method initializes this
	 * 
	 * @return void
	 */
	private void initialize() {
		this.setLayout(new BorderLayout());
		this.setSize(530, 578);
		this.add(getSelectiePanel(), java.awt.BorderLayout.NORTH);
	}

	/**
	 * This method initializes selectiePanel	
	 * 	
	 * @return javax.swing.JPanel	
	 */
	private JPanel getSelectiePanel() {
		if (selectiePanel == null) {
			GridBagConstraints gridBagConstraints15 = new GridBagConstraints();
			gridBagConstraints15.gridx = 0;
			gridBagConstraints15.fill = GridBagConstraints.BOTH;
			gridBagConstraints15.insets = new Insets(5, 5, 5, 5);
			gridBagConstraints15.gridy = 0;
			GridBagConstraints gridBagConstraints14 = new GridBagConstraints();
			gridBagConstraints14.gridx = 0;
			gridBagConstraints14.fill = GridBagConstraints.BOTH;
			gridBagConstraints14.insets = new Insets(5, 5, 5, 5);
			gridBagConstraints14.gridy = 6;
			GridBagConstraints gridBagConstraints13 = new GridBagConstraints();
			gridBagConstraints13.gridx = 0;
			gridBagConstraints13.fill = java.awt.GridBagConstraints.BOTH;
			gridBagConstraints13.insets = new java.awt.Insets(5,5,5,5);
			gridBagConstraints13.gridy = 4;
			GridBagConstraints gridBagConstraints12 = new GridBagConstraints();
			gridBagConstraints12.gridx = 0;
			gridBagConstraints12.insets = new java.awt.Insets(3,3,3,3);
			gridBagConstraints12.fill = java.awt.GridBagConstraints.BOTH;
			gridBagConstraints12.gridy = 5;
			GridBagConstraints gridBagConstraints11 = new GridBagConstraints();
			gridBagConstraints11.gridx = 0;
			gridBagConstraints11.insets = new java.awt.Insets(3,3,3,3);
			gridBagConstraints11.gridy = 3;
			GridBagConstraints gridBagConstraints5 = new GridBagConstraints();
			gridBagConstraints5.gridx = 0;
			gridBagConstraints5.insets = new java.awt.Insets(3,3,3,3);
			gridBagConstraints5.anchor = java.awt.GridBagConstraints.WEST;
			gridBagConstraints5.fill = java.awt.GridBagConstraints.BOTH;
			gridBagConstraints5.gridy = 2;
			GridBagConstraints gridBagConstraints4 = new GridBagConstraints();
			gridBagConstraints4.fill = java.awt.GridBagConstraints.HORIZONTAL;
			gridBagConstraints4.gridy = 2;
			gridBagConstraints4.weightx = 1.0;
			gridBagConstraints4.insets = new java.awt.Insets(3,3,3,3);
			gridBagConstraints4.gridx = 3;
			GridBagConstraints gridBagConstraints31 = new GridBagConstraints();
			gridBagConstraints31.gridx = 2;
			gridBagConstraints31.insets = new java.awt.Insets(3,3,3,3);
			gridBagConstraints31.gridy = 2;
			verenigingLabel = new JLabel();
			verenigingLabel.setText("Vereniging");
			verenigingLabel.setEnabled(false);
			GridBagConstraints gridBagConstraints21 = new GridBagConstraints();
			gridBagConstraints21.gridx = 1;
			gridBagConstraints21.gridy = 2;
			GridBagConstraints gridBagConstraints3 = new GridBagConstraints();
			gridBagConstraints3.fill = java.awt.GridBagConstraints.HORIZONTAL;
			gridBagConstraints3.gridy = 1;
			gridBagConstraints3.weightx = 1.0;
			gridBagConstraints3.insets = new java.awt.Insets(3,3,3,3);
			gridBagConstraints3.gridx = 3;
			GridBagConstraints gridBagConstraints2 = new GridBagConstraints();
			gridBagConstraints2.gridx = 2;
			gridBagConstraints2.insets = new java.awt.Insets(3,3,3,3);
			gridBagConstraints2.gridy = 1;
			programmaLabel = new JLabel();
			programmaLabel.setText("Programma");
			programmaLabel.setEnabled(false);
			GridBagConstraints gridBagConstraints1 = new GridBagConstraints();
			gridBagConstraints1.gridx = 1;
			gridBagConstraints1.gridy = 1;
			GridBagConstraints gridBagConstraints = new GridBagConstraints();
			gridBagConstraints.gridy = 1;
			gridBagConstraints.insets = new java.awt.Insets(5,5,5,5);
			gridBagConstraints.fill = java.awt.GridBagConstraints.BOTH;
			gridBagConstraints.gridx = 0;
			selectiePanel = new JPanel();
			selectiePanel.setLayout(new GridBagLayout());
			selectiePanel.setBorder(javax.swing.BorderFactory.createLineBorder(java.awt.Color.black,3));
			selectiePanel.add(getPerProgrammaCheckBox(), gridBagConstraints1);
			selectiePanel.add(programmaLabel, gridBagConstraints2);
			selectiePanel.add(getProgrammaComboBox(), gridBagConstraints3);
			selectiePanel.add(getStartLijstenButton(), gridBagConstraints);
			selectiePanel.add(getPerVerenigingCheckBox(), gridBagConstraints21);
			selectiePanel.add(verenigingLabel, gridBagConstraints31);
			selectiePanel.add(getVerenigingComboBox(), gridBagConstraints4);
			selectiePanel.add(getStartsPerVerenigingButton(), gridBagConstraints5);
			selectiePanel.add(getAantalDeelnemersPerVerenigingButton(), gridBagConstraints11);
			selectiePanel.add(getStatistiekenButton(), gridBagConstraints12);
			selectiePanel.add(getFactuurButton(), gridBagConstraints13);
			selectiePanel.add(getUitslagButton(), gridBagConstraints14);
			selectiePanel.add(getJButtonBoekje(), gridBagConstraints15);
		}
		return selectiePanel;
	}

	/**
	 * This method initializes startLijstenButton	
	 * 	
	 * @return javax.swing.JButton	
	 */
	private JButton getStartLijstenButton() {
		if (startLijstenButton == null) {
			startLijstenButton = new JButton();
			startLijstenButton.setText("Startlijsten");
			startLijstenButton.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
			startLijstenButton.addActionListener(new StartLijstenButtonActionListener());
		}
		return startLijstenButton;
	}

	/**
	 * This method initializes perProgrammaCheckBox	
	 * 	
	 * @return javax.swing.JCheckBox	
	 */
	private JCheckBox getPerProgrammaCheckBox() {
		if (perProgrammaCheckBox == null) {
			perProgrammaCheckBox = new JCheckBox();
			perProgrammaCheckBox.addActionListener(new PerProgrammaCheckBoxActionListener());
		}
		return perProgrammaCheckBox;
	}

	/**
	 * This method initializes programmaComboBox	
	 * 	
	 * @return javax.swing.JComboBox	
	 */
	private JComboBox getProgrammaComboBox() {
		if (programmaComboBox == null) {
			programmaComboBox = new JComboBox();
			programmaComboBox.setEnabled(false);
			programmaComboBox.addActionListener(new ProgrammaComboActionListener());
			boolean hasBegun = SConnection.getConnection().hasBegun();
			if( ! hasBegun ) { SConnection.begin(); }
			SPreparedStatement stmt = Programmanummers.meta.select(
					"1=1", "ProgNr");
			SResultSet res = stmt.execute();
			programmaComboBox .addItem("<Kies programma>");
			while (res.hasNext()) {
				Programmanummers prg = (Programmanummers)res.getRecord();
				int pnr = prg.get_fldPrognr().intValue();
				programmaComboBox .addItem(
						(pnr<10?"0":"")+pnr+" "+prg.get_fldTitel());
			}
			SConnection.commit();
		}
		return programmaComboBox;
	}

	/**
	 * This method initializes perVerenigingCheckBox	
	 * 	
	 * @return javax.swing.JCheckBox	
	 */
	private JCheckBox getPerVerenigingCheckBox() {
		if (perVerenigingCheckBox == null) {
			perVerenigingCheckBox = new JCheckBox();
			perVerenigingCheckBox.addActionListener(new PerVerenigingCheckBoxActionListener());
		}
		return perVerenigingCheckBox;
	}

	/**
	 * This method initializes verenigingComboBox	
	 * 	
	 * @return javax.swing.JComboBox	
	 */
	private JComboBox getVerenigingComboBox() {
		if (verenigingComboBox == null) {
			verenigingComboBox = new JComboBox();
			verenigingComboBox.setEnabled(false);
			verenigingComboBox.addActionListener(new VerenigingComboActionListener());
			boolean hasBegun = SConnection.getConnection().hasBegun();
			if( ! hasBegun ) { SConnection.begin(); }
			SPreparedStatement stmt = Vereniging.meta.select(
					"1=1", "DepotNr");
			SResultSet res = stmt.execute();
			verenigingComboBox.addItem("<Kies vereniging>");
			while (res.hasNext()) {
				Vereniging prg = (Vereniging)res.getRecord();
				int pnr = prg.get_fldDepotnr().intValue();
				verenigingComboBox .addItem(
						(pnr<10000?"0":"")+pnr+" "+prg.get_fldNaam());
			}
			SConnection.commit();
		}
		return verenigingComboBox;
	}

	/**
	 * This method initializes startsPerVerenigingButton	
	 * 	
	 * @return javax.swing.JButton	
	 */
	private JButton getStartsPerVerenigingButton() {
		if (startsPerVerenigingButton == null) {
			startsPerVerenigingButton = new JButton();
			startsPerVerenigingButton.setText("Starts per Vereniging");
			startsPerVerenigingButton.addActionListener(new StartsPerVerenigingButtonActionListener());
		}
		return startsPerVerenigingButton;
	}

	/**
	 * This method initializes aantalDeelnemersPerVerenigingButton	
	 * 	
	 * @return javax.swing.JButton	
	 */
	private JButton getAantalDeelnemersPerVerenigingButton() {
		if (aantalDeelnemersPerVerenigingButton == null) {
			aantalDeelnemersPerVerenigingButton = new JButton();
			aantalDeelnemersPerVerenigingButton.setText("Aantal deelnemers per Vereniging");
			aantalDeelnemersPerVerenigingButton
					.addActionListener(new AantalDeelnemersPerVerenigingButtonActionListener());
		}
		return aantalDeelnemersPerVerenigingButton;
	}

	/**
	 * This method initializes statistiekenButton	
	 * 	
	 * @return javax.swing.JButton	
	 */
	private JButton getStatistiekenButton() {
		if (statistiekenButton == null) {
			statistiekenButton = new JButton();
			statistiekenButton.setText("Statistieken");
			statistiekenButton.addActionListener(new StatistiekenButtonActionListener());
		}
		return statistiekenButton;
	}

	/**
	 * This method initializes factuurButton	
	 * 	
	 * @return javax.swing.JButton	
	 */
	private JButton getFactuurButton() {
		if (factuurButton == null) {
			factuurButton = new JButton();
			factuurButton.setText("Factuur");
			factuurButton.addActionListener(new FactuurButtonActionListener());
		}
		return factuurButton;
	}

	/**
	 * This method initializes uitslagButton	
	 * 	
	 * @return javax.swing.JButton	
	 */
	private JButton getUitslagButton() {
		if (uitslagButton == null) {
			uitslagButton = new JButton();
			uitslagButton.setText("Uitslag voor publicatie");
			uitslagButton.addActionListener(new UitslagButtonActionListener());
		}
		return uitslagButton;
	}

	/**
	 * This method initializes jButtonBoekje	
	 * 	
	 * @return javax.swing.JButton	
	 */
	private JButton getJButtonBoekje() {
		if (jButtonBoekje == null) {
			jButtonBoekje = new JButton();
			jButtonBoekje.setText("Export Boekje");
			jButtonBoekje.addActionListener(new BoekjeButtonActionListener());
		}
		return jButtonBoekje;
	}

	private void BerekenKostenPerVereniging() {
		// Maak KostenPerVer tabel leeg
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		SConnection.rawUpdateDB("DELETE FROM KostenPerVer");
		SConnection.commit();
		// Bepaal totaal kosten per vereniging 
		hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			// Get the Startkosten
			StartkostenBean k = new StartkostenBean();
			k.setWedstrijdid(1);
			k.read();
			
			Map<Integer,Record> kosten = new HashMap<Integer,Record>();
			
			Statement stmt = JDBCcon.createStatement();
			// Nr of Leencaps
			ResultSet res = stmt.executeQuery("SELECT DepotNr, COUNT(Vast) as LeenCaps FROM start WHERE Vast=0 GROUP BY DepotNr");
			while( res.next() ) {
				if( kosten.containsKey(res.getInt("DepotNr")) ) {
					kosten.get(res.getInt("DepotNr")).kosten3 = res.getInt("Leencaps") * k.getKostenstart3().doubleValue();
				} else {
					kosten.put(res.getInt("DepotNr"), new Record(0.0, 0.0, res.getInt("Leencaps") * k.getKostenstart3().doubleValue(), 0.0, 0.0, 0));
				}
			}
			// Nr of Starts ONK
			res = stmt.executeQuery("SELECT DepotNr, COUNT(StartNr) AS NrStarts FROM start WHERE ProgNr<=15 GROUP BY DepotNr");
			while( res.next() ) {
				if( kosten.containsKey(res.getInt("DepotNr")) ) {
					kosten.get(res.getInt("DepotNr")).kosten4 = res.getInt("NrStarts") * k.getKostenstart4().doubleValue();
				} else {
					kosten.put(res.getInt("DepotNr"), new Record(0.0, 0.0, 0.0, res.getInt("NrStarts") * k.getKostenstart4().doubleValue(), 0.0, 0));
				}
			}
			// Nr of Starts HSZ
			res = stmt.executeQuery("SELECT A.DepotNr, COUNT(A.NrStarts) AS Nr1Starts FROM "+
					"(SELECT DepotNr, StartNr, COUNT(StartNr) AS NrStarts FROM olowus.start WHERE ProgNr>15 GROUP BY DepotNr, StartNr) AS A "+
					"WHERE NrStarts=1 GROUP BY A.DepotNr");
			while( res.next() ) {
				if( kosten.containsKey(res.getInt("DepotNr")) ) {
					kosten.get(res.getInt("DepotNr")).kosten1 = res.getInt("Nr1Starts") * k.getKostenstart1().doubleValue();
				} else {
					kosten.put(res.getInt("DepotNr"), new Record(res.getInt("Nr1Starts") * k.getKostenstart1().doubleValue(), 0.0, 0.0, 0.0, 0.0, 0));
				}
			}
			res = stmt.executeQuery("SELECT A.DepotNr, COUNT(A.NrStarts) AS Nr2Starts FROM "+
					"(SELECT DepotNr, StartNr, COUNT(StartNr) AS NrStarts FROM olowus.start WHERE ProgNr>15 GROUP BY DepotNr, StartNr) AS A "+
					"WHERE NrStarts>1 GROUP BY A.DepotNr");
			while( res.next() ) {
				if( kosten.containsKey(res.getInt("DepotNr")) ) {
					kosten.get(res.getInt("DepotNr")).kosten2 = res.getInt("Nr2Starts") * k.getKostenstart2().doubleValue();
				} else {
					kosten.put(res.getInt("DepotNr"), new Record(0.0, res.getInt("Nr2Starts") * k.getKostenstart2().doubleValue(), 0.0, 0.0, 0.0, 0));
				}
			}
			// Nr of Deelnemers
			res = stmt.executeQuery("SELECT A.DepotNr, A.StartNr, COUNT(*) as nrdlnmrs FROM "+
					"(SELECT DISTINCT DepotNr, StartNr FROM start ORDER BY DepotNr) AS A GROUP BY A.DepotNr");
			while( res.next() ) {
				if( kosten.containsKey(res.getInt("DepotNr")) ) {
					kosten.get(res.getInt("DepotNr")).nrDeelnemers = res.getInt("nrdlnmrs");
				} else {
					kosten.put(res.getInt("DepotNr"), new Record(0.0, 0.0, 0 , 0.0, 0.0, res.getInt("nrdlnmrs")));
				}
			}
			Statement ustmt = JDBCcon.createStatement();
			for( Integer dnr : kosten.keySet() ) {
				Record r = kosten.get(dnr);
				ustmt.executeUpdate("INSERT INTO KostenPerVer (DepotNr,Kosten1,Kosten2,Kosten3,Kosten4,AantalDeelnemers)" +
						" VALUES("+ dnr + "," + r.kosten1 + "," + r.kosten2 + "," + r.kosten3 + "," + r.kosten4 +
						"," + r.nrDeelnemers+")");
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		SConnection.commit();
	}
	
	private class Record {
		public double kosten1;
		public double kosten2;
		public double kosten3;
		public double kosten4;
		public double kosten5;
		public int nrDeelnemers;
		
		public Record(double kosten1, double kosten2, double kosten3, double kosten4, double kosten5, int nrdeelnmrs) {
			this.kosten1 = kosten1;
			this.kosten2 = kosten2;
			this.kosten3 = kosten3;
			this.kosten4 = kosten4;
			this.kosten5 = kosten5;
			this.nrDeelnemers = nrdeelnmrs;
		}
	}
}  //  @jve:decl-index=0:visual-constraint="20,12,0,0"
